package com.steven.team.dao

import com.steven.team.pojo.*
import org.apache.ibatis.annotations.*
import org.springframework.stereotype.Repository

@Repository
interface TeamDao {

    @Insert("insert into team_info(teamName, matchId, belongUserId, createTime) value (#{teamName},#{matchId},#{belongUserId},#{createTime}) ON DUPLICATE KEY UPDATE createTime=#{createTime}")
    fun addTeam(team: TeamInfo)

    @Update("update team_info set teamName = #{name} where id=#{teamId}")
    fun updateTeamName(name: String, teamId: Long)

    @Update("update team_info set status = #{status} where id=#{teamId}")
    fun updateTeamStatus(teamId: Long, status: Boolean)

    @Insert("insert into team_apply(teamId, uid,createTime) value (#{teamId},#{uid},#{createTime}) ON DUPLICATE KEY UPDATE status=null")
    fun applyToTeam(apply: TeamApply)

    @Select("select resB.*, uname leaderName, iconUrl leaderIconUrl\n" +
            "from (select res.*, match_info.name matchName\n" +
            "      from (select resA.id                as id,\n" +
            "                   resA.teamName          as teamName,\n" +
            "                   resA.matchId           as matchId,\n" +
            "                   resA.belongUserId      as belongUserId,\n" +
            "                   resA.createTime        as createTime,\n" +
            "                   resA.status            as status,\n" +
            "                   count(team_member.uid) as memberNum\n" +
            "            from (select *\n" +
            "                  from team_info\n" +
            "                  where belongUserId = #{user}) as resA\n" +
            "                     left join team_member\n" +
            "                               on resA.id = team_member.teamId\n" +
            "            group by (resA.id)\n" +
            "            union\n" +
            "            select team_info.id           as id,\n" +
            "                   team_info.teamName     as teamName,\n" +
            "                   team_info.matchId      as matchId,\n" +
            "                   team_info.belongUserId as belongUserId,\n" +
            "                   team_info.createTime   as createTime,\n" +
            "                   team_info.status       as status,\n" +
            "                   count(team_member.uid) as memberNum\n" +
            "            from team_info\n" +
            "                     left join team_member\n" +
            "                               on team_member.teamId = team_info.id\n" +
            "            where team_member.uid = #{user}\n" +
            "            group by (team_info.id)\n" +
            "           ) as res\n" +
            "               left join match_info on matchId = match_info.id) as resB\n" +
            "         left join user_detail on resB.belongUserId = user_detail.uid\n" +
            "order by convert(teamName using gbk)")
    fun queryUserTeam(user: Long): List<TeamInfo>

    @Update("update team_apply set status = #{pass} where id=#{applyId}")
    fun updateApplyToTeam(applyId: Long, pass: Boolean)

    @Select("select resB.*, iconUrl, uname\n" +
            "from (select resA.*, teamName\n" +
            "      from (select id, teamId, uid, createTime, status\n" +
            "            from team_apply\n" +
            "            where status IS NULL\n" +
            "              and teamId in (select id from team_info where belongUserId = #{owner})) as resA\n" +
            "               left join team_info on teamId = team_info.id) as resB\n" +
            "         left join user_detail on resB.uid = user_detail.uid\n" +
            "order by resB.createTime desc")
    fun queryApplyToMe(owner: Long): List<TeamApply>

    @Insert("insert into match_info(name,belong,rank,signUpStartTime,signUpEndTime,matchStartTime,matchEndTime,url) " +
            "value(#{name},#{belong},#{rank},#{signUpStartTime},#{signUpEndTime},#{matchStartTime},#{matchEndTime},#{url})")
    @Options(useGeneratedKeys = true, keyProperty = "matchInfo.id", keyColumn = "id")
    fun addMatch(matchInfo: MatchInfo)

    @Select("select id, teamId, uid, createTime, status\n" +
            "from team_apply\n" +
            "where id= #{id}")
    fun queryApplyByID(id: Long): List<TeamApply>

    @Insert("insert into team_member(teamId,uid,createTime) value (#{teamId},#{userId},#{createTime}) ON DUPLICATE KEY UPDATE createTime = #{createTime}")
    fun addTeamMemBer(teamId: Long, userId: Long, createTime: Long)

    @Select("select resB.*, name as matchName,belong,rank,signUpStartTime,signUpEndTime,matchStartTime,matchEndTime,url\n" +
            "from (select uname as leaderName, iconUrl leaderIconUrl, res.*\n" +
            "      from (select id, teamName, matchId, belongUserId, status\n" +
            "            from team_info\n" +
            "            where id = #{teamId}) as res\n" +
            "               left join user_detail on user_detail.uid = belongUserId) as resB\n" +
            "         left join match_info on resB.matchId = match_info.id")
    fun queryTeamAndLeader(teamId: Long): List<TeamAndLeader>

    @Select("select user_detail.uid, uname, iconUrl\n" +
            "from (select uid from team_member where teamId = #{teamId}) as users\n" +
            "         left join user_detail on users.uid = user_detail.uid\n" +
            "order by convert(uname using gbk)")
    fun queryTeamMembers(teamId: Long): List<TeamMember>

    @Select("select * from match_info where name = #{name}")
    fun searchMatchByName(name: String): List<MatchInfo>

    @Select("select *\n" +
            "from match_info\n" +
            "where name like concat('%', CONCAT(#{name}, '%'))\n" +
            "  and id > #{lastId}\n" +
            "order by id\n" +
            "limit 10")
    fun searchMatchList(name: String, lastId: Long): List<MatchInfo>

    @Select("select teamId,\n" +
            "       teamName,\n" +
            "       pageViews,\n" +
            "       resC.status,\n" +
            "       matchId,\n" +
            "       matchName,\n" +
            "       memberNum,\n" +
            "       belongUserId,\n" +
            "       uname,\n" +
            "       iconUrl,\n" +
            "       userType\n" +
            "from (select resB.*, name as matchName, uname, iconUrl, userType\n" +
            "      from (select resA.*, teamName, count(tm.uid) memberNum, matchId, belongUserId, team_info.createTime, status\n" +
            "            from (select teamId, pageViews from team_page_views) as resA\n" +
            "                     left join team_info on resA.teamId = team_info.id\n" +
            "                     left join team_member tm on team_info.id = tm.teamId\n" +
            "            group by resA.teamId) as resB\n" +
            "               left join match_info on resB.matchId = match_info.id\n" +
            "               left join user_detail on belongUserId = user_detail.uid\n" +
            "      where school in (select school from user_detail where uid = #{uid})) as resC where status = true\n" +
            "order by pageViews desc\n" +
            "limit 5")
    fun searchTop5TeamInMySchool(uid: Long): List<TeamTop5Detail>

    @Select("select teamId,\n" +
            "       teamName,\n" +
            "       pageViews,\n" +
            "       resC.status,\n" +
            "       matchId,\n" +
            "       matchName,\n" +
            "       memberNum,\n" +
            "       belongUserId,\n" +
            "       uname,\n" +
            "       iconUrl,\n" +
            "       userType\n" +
            "from (select resB.*, name as matchName, uname, iconUrl, userType\n" +
            "      from (select resA.*, teamName, count(tm.uid) memberNum, matchId, belongUserId, team_info.createTime, status\n" +
            "            from (select teamId, pageViews from team_page_views) as resA\n" +
            "                     left join team_info on resA.teamId = team_info.id\n" +
            "                     left join team_member tm on team_info.id = tm.teamId\n" +
            "            group by resA.teamId) as resB\n" +
            "               left join match_info on resB.matchId = match_info.id\n" +
            "               left join user_detail on belongUserId = user_detail.uid\n" +
            "      where school not in (select school from user_detail where uid = #{uid})) as resC where status = true\n" +
            "order by pageViews desc\n" +
            "limit 5")
    fun searchTop5TeamNotInMySchool(uid: Long): List<TeamTop5Detail>

    @Select("select teamId,\n" +
            "       teamName,\n" +
            "       pageViews,\n" +
            "       resC.status,\n" +
            "       matchId,\n" +
            "       matchName,\n" +
            "       memberNum,\n" +
            "       belongUserId,\n" +
            "       uname,\n" +
            "       iconUrl,\n" +
            "       userType\n" +
            "from (select resB.*, name as matchName, uname, iconUrl, userType\n" +
            "      from (select resA.*, teamName, count(tm.uid) memberNum, matchId, belongUserId, team_info.createTime, status\n" +
            "            from (select teamId, pageViews from team_page_views) as resA\n" +
            "                     left join team_info on resA.teamId = team_info.id\n" +
            "                     left join team_member tm on team_info.id = tm.teamId\n" +
            "            group by resA.teamId) as resB\n" +
            "               left join match_info on resB.matchId = match_info.id\n" +
            "               left join user_detail on belongUserId = user_detail.uid\n" +
            "      ) as resC where status = true\n" +
            "order by pageViews desc\n" +
            "limit 5")
    fun searchTop5TeamWithoutLogin(): List<TeamTop5Detail>

    @Insert("insert into team_page_views(teamId, pageViews) VALUE (#{teamId}, 1)\n" +
            "on duplicate key update pageViews=pageViews + 1")
    fun addTeamPageViews(teamId: Long): Int

    @Insert("insert into team_collection(teamId, uid, createTime) VALUE (#{teamId},#{uid},#{createTime})\n" +
            "on duplicate key update createTime=#{createTime}")
    fun addToCollection(teamId: Long, uid: Long, createTime: Long = System.currentTimeMillis()): Int

    @Delete("delete from team_collection where teamId=#{teamId} and uid=#{uid}")
    fun deleteCollectionById(uid: Long, teamId: Long): Int

    @Select("select id, teamId, teamName, uname, iconUrl, createTime\n" +
            "from (select tc.*, teamName, belongUserId\n" +
            "      from (select * from team_collection where uid = #{uid}) as tc\n" +
            "               left join team_info on teamId = team_info.id) as ti\n" +
            "         left join user_detail on user_detail.uid = ti.belongUserId\n" +
            "where createTime<#{lastCreateTime}\n" +
            "order by createTime desc\n" +
            "limit 20")
    fun selectCollectionsByUser(uid: Long, lastCreateTime: Long): List<TeamCollection>

    @Select("select * from team_collection where uid = #{uid} and teamId=#{teamId}")
    fun selectCollectionsByUserAndTeamId(uid: Long, teamId: Long): List<TeamCollection>

    @Select("select * from user_ban where uid = #{uid}")
    fun selectUserBanByUid(uid: Long): List<UserBan>

    @Delete("delete from user_ban where uid=#{uid}")
    fun deleteUserBanByUid(uid: Long): Int

    @Insert("insert into user_ban(uid, createTime) VALUE (#{uid},#{createTime})\n" +
            "on duplicate key update createTime=#{createTime}")
    fun addUserBan(uid: Long, createTime: Long): Int

    @Select("select team_info.id,\n" +
            "       matchId,\n" +
            "       name    matchName,\n" +
            "       belong,\n" +
            "       rank,\n" +
            "       signUpStartTime,\n" +
            "       signUpEndTime,\n" +
            "       matchStartTime,\n" +
            "       matchEndTime,\n" +
            "       url,\n" +
            "       teamName,\n" +
            "       belongUserId,\n" +
            "       team_info.status,\n" +
            "       uname   leaderName,\n" +
            "       iconUrl leaderIconUrl\n" +
            "from team_info\n" +
            "         left join user_detail ud on team_info.belongUserId = ud.uid\n" +
            "         left join match_info mi on team_info.matchId = mi.id\n" +
            "where school in (select school from user_detail where uid = #{uid}) and team_info.status=1\n" +
            "order by createTime desc")
    fun searchSameSchoolTeam(uid: Long): List<TeamInfo>

    @Select("select team_info.id,\n" +
            "       matchId,\n" +
            "       name          matchName,\n" +
            "       teamName,\n" +
            "       belongUserId,\n" +
            "       team_info.status,\n" +
            "       uname         leaderName,\n" +
            "       iconUrl       leaderIconUrl,\n" +
            "       team_info.createTime,\n" +
            "       count(tm.uid) memberNum\n" +
            "from team_info\n" +
            "         left join user_detail ud on team_info.belongUserId = ud.uid\n" +
            "         left join match_info mi on team_info.matchId = mi.id\n" +
            "         left join team_member tm on team_info.id = tm.teamId\n" +
            "where team_info.status = 1\n" +
            "  and team_info.createTime < #{lastCreateTime}\n" +
            "  and belongUserId != #{uid}\n" +
            "group by team_info.id\n" +
            "order by team_info.createTime desc\n" +
            "limit 10")
    fun searchNewCreateTeam(uid: Long, lastCreateTime: Long): List<TeamInfo>
}